package com.xtedu.examproject.service;

import com.xtedu.examproject.dao.base.StudentExamRecordDao;
import com.xtedu.examproject.dao.impl.StudentExamRecordDaoImpl;
import com.xtedu.examproject.entity.StudentExamRecord;
import com.xtedu.examproject.entity.StudentExamRecordDTO;
import com.xtedu.examproject.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentExamRecordService {
    private StudentExamRecordDao studentExamRecordDao;

    public StudentExamRecordService() {
        studentExamRecordDao = new StudentExamRecordDaoImpl();
    }

    public int insertStudentExamRecord(StudentExamRecord studentExamRecord) throws Exception {
        return studentExamRecordDao.insert(studentExamRecord);
    }

    public int deleteStudentExamRecord(StudentExamRecord studentExamRecord) throws Exception {
        return studentExamRecordDao.delete(studentExamRecord);
    }

    public int updateStudentExamRecord(StudentExamRecord studentExamRecord) throws Exception {
        return studentExamRecordDao.update(studentExamRecord);
    }

    public StudentExamRecord findStudentExamRecordById(int id) throws Exception {
        return studentExamRecordDao.findById(id);
    }

    public List<StudentExamRecord> findAllStudentExamRecord() throws Exception {
        return studentExamRecordDao.findAll();
    }

    /*
     * 返回DTO列表
     * */
    public static List<StudentExamRecordDTO> getStudentExamRecordsByStudentId(int studentId) throws SQLException {
        String sql = """
                SELECT  
                    ser.theory_score,
                    ser.practice_score,
                    ser.exam_result,
                    ser.create_date,
                    em.exam_name,
                    em.`start` as exam_start,
                    cs.course_name,
                    s.`name` as student_name,
                    cl.class_name,
                    r.role_name,
                    s.id_card,
                    s.phone
                FROM student_exam_records ser 
                LEFT JOIN examinations em ON ser.exam_id=em.id 
                LEFT JOIN courses cs ON em.course_id=cs.id 
                LEFT JOIN students s ON s.id=ser.stu_id 
                LEFT JOIN classes cl ON cl.id=ser.class_id 
                LEFT JOIN users u ON u.info_id=ser.stu_id 
                LEFT JOIN roles r ON r.id=u.role_id 
                WHERE ser.stu_id=?
                ORDER BY em.`start` ASC;
                """;

        List<StudentExamRecordDTO> records = new ArrayList<>();

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, studentId);

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    StudentExamRecordDTO record = new StudentExamRecordDTO();
                    record.setTheoryScore(rs.getFloat("theory_score"));
                    record.setPracticeScore(rs.getFloat("practice_score"));
                    record.setExamResult(rs.getBoolean("exam_result"));
                    record.setCreateDate(rs.getDate("create_date"));
                    record.setExamName(rs.getString("exam_name"));
                    record.setStartTime(rs.getTimestamp("exam_start"));
                    record.setCourseName(rs.getString("course_name"));
                    record.setStuName(rs.getString("student_name"));
                    record.setClassName(rs.getString("class_name"));
                    record.setRole_name(rs.getString("role_name"));
                    record.setIdCard(rs.getString("id_card"));
                    record.setPhone(rs.getString("phone"));
                    records.add(record);
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("查询学生考试记录失败", e);
        }
        return records;
    }

    /*
     * 获取所有的考试记录
     * */
    public static List<StudentExamRecordDTO> getAllStudentExamRecords() throws SQLException {
        String sql = """
                
                SELECT
                  em.course_id,
                  cs.course_name,
                  ser.exam_id,
                  em.exam_name,
                  ser.stu_id,
                   s.`name`,
                   s.id_card,
                   ser.class_id,
                  c.class_name,
                  ser.theory_score,
                 ser.practice_score,
                 ser.exam_result,
                 t.teacher_name
                FROM
                  examinations em
                  LEFT JOIN courses cs ON cs.id = em.course_id
                  LEFT JOIN student_exam_records ser ON ser.exam_id = em.id
                  LEFT JOIN students s ON s.id = ser.stu_id
                  LEFT JOIN classes c ON c.id = ser.class_id
                  LEFT JOIN teachers_courses tc ON tc.course_id=c.course_id
                  LEFT JOIN teachers t ON t.id=tc.teacher_id
                  ;
                """;

        List<StudentExamRecordDTO> records = new ArrayList<>();

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    StudentExamRecordDTO record = new StudentExamRecordDTO();
                    record.setCourseId(rs.getInt("course_id"));
                    record.setCourseName(rs.getString("course_name"));
                    record.setExamId(rs.getInt("exam_id"));
                    record.setExamName(rs.getString("exam_name"));
                    record.setStuId(rs.getInt("stu_id"));
                    record.setStuName(rs.getString("name"));
                    record.setIdCard(rs.getString("id_card"));
                    record.setClassId(rs.getInt("class_id"));
                    record.setClassName(rs.getString("class_name"));
                    record.setTheoryScore(rs.getFloat("theory_score"));
                    record.setPracticeScore(rs.getFloat("practice_score"));
                    record.setExamResult(rs.getBoolean("exam_result"));
                    record.setTeacherName(rs.getString("teacher_name"));
                    records.add(record);
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("查询学生考试记录失败", e);
        }
        return records;

    }

    /*
     * 通过考试名称和学生idCard，课程名称 来获取对应学生考试记录,返回一个DTO对象(前提是能够保证学生idCard是唯一的，不然返回的是一个列表)
     * */
    public static List<StudentExamRecordDTO> getStudentExamRecordsByNameAndIdCardAndCourseName(String examName, String idCard, String courseName) throws SQLException {
        String sql = """
                SELECT  
                    ser.theory_score,
                    ser.practice_score,
                    ser.exam_result,
                    ser.create_date,
                    em.exam_name,
                    em.`start` as exam_start,
                    cs.course_name,
                    s.`name` as student_name,
                    cl.class_name,
                    r.role_name,
                    s.id_card,
                    s.phone
                FROM student_exam_records ser 
                LEFT JOIN examinations em ON ser.exam_id=em.id 
                LEFT JOIN courses cs ON em.course_id=cs.id 
                LEFT JOIN students s ON s.id=ser.stu_id 
                LEFT JOIN classes cl ON cl.id=ser.class_id 
                LEFT JOIN users u ON u.info_id=ser.stu_id 
                LEFT JOIN roles r ON r.id=u.role_id 
                WHERE cs.course_name=? AND em.exam_name = ? AND s.id_card=?
                """;
        //前期测试数据会有重复的，这里取最后一条
        List<StudentExamRecordDTO> records = new ArrayList<>();
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, courseName);
            pstmt.setString(2, examName);
            pstmt.setString(3, idCard);
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    StudentExamRecordDTO record = new StudentExamRecordDTO();
                    record.setTheoryScore(rs.getFloat("theory_score"));
                    record.setPracticeScore(rs.getFloat("practice_score"));
                    record.setExamResult(rs.getBoolean("exam_result"));
                    record.setCreateDate(rs.getDate("create_date"));
                    record.setExamName(rs.getString("exam_name"));
                    record.setStartTime(rs.getTimestamp("exam_start"));
                    record.setCourseName(rs.getString("course_name"));
                    record.setStuName(rs.getString("student_name"));
                    record.setClassName(rs.getString("class_name"));
                    record.setRole_name(rs.getString("role_name"));
                    record.setIdCard(rs.getString("id_card"));
                    record.setPhone(rs.getString("phone"));
                    records.add(record);
                }
            }
            return records;
        }
    }

    /*
     * 更新学生考试记录的实践分
     * */
    public static int updateStudentExamRecordPracticeScore(int courseId, String idCard, int examId, float practiceScore) throws SQLException {
        // 通过考试名称和学生idCard，课程名称 来获取学生考试记录,取最后一条记录
        String sql = """
                UPDATE student_exam_records ser
                 JOIN examinations em ON ser.exam_id = em.id
                 JOIN courses cs ON em.
                 course_id = cs.id
                 JOIN
                 students s ON ser.stu_id 
                SET ser.practice_score = ?
                WHERE em.course_id =?
                   AND ser.exam_id =?
                   AND ser.stu_id=(SELECT s.id FROM students s WHERE s.id_card = ?);
                """;
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setFloat(1, practiceScore);
            pstmt.setInt(2, courseId);
            pstmt.setInt(3, examId);
            pstmt.setString(4, idCard);
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException("更新学生考试记录的实践分失败", e);
        }
    }

    /*
    *通过考试id,课程id 来获取该课程下的某一次考试学生考试记录,返回一个DTO对象
    * */
    public static List<StudentExamRecordDTO> getStudentExamRecordsByExamIdAndCourseId(int examId, int courseId) throws SQLException {
        String sql = """
                SELECT
                  em.course_id,
                  cs.course_name,
                  ser.exam_id,
                  em.exam_name,
                  ser.stu_id,
                  s.`name`,
                  s.id_card,
                  ser.class_id,
                  c.class_name,
                  ser.theory_score,
                  ser.practice_score,
                  ser.exam_result,
                  t.teacher_name,
                  s.gender,
                  ser.create_date
                FROM
                  examinations em
                  LEFT JOIN courses cs ON cs.id = em.course_id
                  LEFT JOIN student_exam_records ser ON ser.exam_id = em.id
                  LEFT JOIN students s ON s.id = ser.stu_id
                  LEFT JOIN classes c ON c.id = ser.class_id
                  LEFT JOIN teachers_courses tc ON tc.course_id = c.course_id
                  LEFT JOIN teachers t ON t.id = tc.teacher_id
                WHERE
                  em.id = ?
                  AND cs.id = ?
                """;

        List<StudentExamRecordDTO> records = new ArrayList<>();

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, examId);
            pstmt.setInt(2, courseId);
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    StudentExamRecordDTO record = new StudentExamRecordDTO();
                    record.setCourseId(rs.getInt("course_id"));
                    record.setCourseName(rs.getString("course_name"));
                    record.setExamId(rs.getInt("exam_id"));
                    record.setExamName(rs.getString("exam_name"));
                    record.setStuId(rs.getInt("stu_id"));
                    record.setStuName(rs.getString("name"));
                    record.setIdCard(rs.getString("id_card"));
                    record.setClassId(rs.getInt("class_id"));
                    record.setClassName(rs.getString("class_name"));
                    record.setTheoryScore(rs.getFloat("theory_score"));
                    record.setPracticeScore(rs.getFloat("practice_score"));
                    record.setExamResult(rs.getBoolean("exam_result"));
                    record.setTeacherName(rs.getString("teacher_name"));
                    record.setGender(rs.getBoolean("gender"));
                    record.setCreateDate(rs.getDate("create_date"));
                    records.add(record);
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("查询学生考试记录失败", e);
        }
        return records;
    }

}
